﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Diagnostics;

namespace DatabaseModule
{
    //Database interface, with extended functionality specific to SQL-driven databases (as opposed to flat-file or other)
    public abstract class SQLDatabase : DAO
    {
        //Store the connection string
        protected string connectionString;

        //Store the most recent error string
        protected string errorStr;

        //Store whether or not we are in verbose mode
        protected bool verboseMode;

        //Derived classes must implement these functions
        protected abstract DbConnection createConnection();
        protected abstract DbCommand createCommand(string sql, DbConnection conn);
        protected abstract void addParameter(ref DbCommand cmd, string name, object value);
        protected abstract string applyLimit(string sql, int limit);

        //For debug use: when set to true, all SQL statements are printed to the console
        public void SetVerboseMode(bool mode)
        {
            verboseMode = mode;
        }

        //Test that the database connection can be established
        public bool TestConnection()
        {
            DbConnection conn = createConnection();
            return (conn != null);
        }

        //Data Accessor Functions

        //Retrieve a single value from the database
        public string GetValue(string table, string field, KeyValues keysAndVals)
        {
            //The string to be returned
            string returnVal = null;

            //Retrieve the result-set using a limit of 1
            List<Dictionary<string, string>> rows = GetRows(table, new string[] { field }, keysAndVals, 1);

            //Extract the first row's first value
            try
            {
                returnVal = rows[0].Values.ElementAt(0);
            }
            catch (Exception)
            {
                returnVal = null;
            }

            //Return the retrieved value
            return returnVal;
        }

        //Retrieve the first row of results from the database
        public Dictionary<string, string> GetRow(string table, string[] fields, KeyValues keysAndVals)
        {
            //The row to be returned
            Dictionary<string, string> row = new Dictionary<string, string>();

            //Retrieve the result-set using a limit of 1
            List<Dictionary<string, string>> rows = GetRows(table, fields, keysAndVals, 1);

            //Extract the first row
            if (rows.Count > 0)
            {
                row = rows[0];
            }

            //Return the retrieved row
            return row;
        }

        //Retrieve all results from the database
        public List<Dictionary<string, string>> GetRows(string table, string[] fields, KeyValues keysAndVals, int limit = 0)
        {
            //The list of rows to be returned
            List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>();

            //Create a new connection
            DbConnection conn = createConnection();
            if (conn != null)
            {
                using (conn)
                {
                    //Create the command
                    using (DbCommand command = generateSelectStatement(table, fields, keysAndVals, limit, conn))
                    {
                        try
                        {
                            //Create a data reader
                            using (DbDataReader reader = command.ExecuteReader())
                            {
                                //Read the data
                                while (reader.Read())
                                {
                                    //Create a new row
                                    Dictionary<string, string> row = new Dictionary<string, string>();

                                    //Save the values to be returned
                                    for (int i = 0; i < fields.Length; ++i)
                                    {
                                        try
                                        {
                                            //Map the value to the corresponding field
                                            row[fields[i]] = reader.GetString(i);
                                        }
                                        catch (Exception)
                                        {
                                            //The value was null
                                            row[fields[i]] = null;
                                        }
                                    }

                                    //Add the row to the list of rows
                                    rows.Add(row);
                                }
                            }
                        }
                        catch (Exception e)
                        {
                            //Record the error
                            errorStr = e.Message;
                        }

                    }
                }
            }

            //Return the retrieved rows
            return rows;
        }


        //Data Mutator Functions

        //Insert a new row into the database
        public bool InsertRow(string table, KeyValues fieldsAndVals)
        {
            //Build the list of parameter names for the values to insert
            string[] valParams = new string[fieldsAndVals.Count];
            for (int i = 0; i < fieldsAndVals.Count; ++i)
            {
                valParams[i] = "@value" + i.ToString();
            }

            //Build the SQL statement
            string sql = "INSERT INTO " + table + " (" + String.Join(", ", fieldsAndVals.KeysToArray()) + ") VALUES (" + String.Join(", ", valParams) + ")";

            //Create the parameter binding mappings
            Dictionary<string, KeyValues> bindingMap = new Dictionary<string, KeyValues>();
            bindingMap.Add("value", fieldsAndVals);

            //Perform the query and return whether or not it succeeded
            return PerformQuery(sql, bindingMap);
        }

        //Update the value(s) of certain field(s) in any rows where key(s) match value(s)
        public bool UpdateRows(string table, KeyValues fieldsAndVals, KeyValues keysAndVals)
        {
            //Build the SQL statement
            string sql = "UPDATE " + table + " SET " + prepareKeysAndVals(fieldsAndVals, "newVal", ", ") + " WHERE (" + prepareKeysAndVals(keysAndVals, "keyVal") + ")";

            //Create the parameter binding mappings
            Dictionary<string, KeyValues> bindingMap = new Dictionary<string, KeyValues>();
            bindingMap.Add("newVal", fieldsAndVals);
            bindingMap.Add("keyVal", keysAndVals);

            //Perform the query and return whether or not it succeeded
            return PerformQuery(sql, bindingMap);
        }

        //Delete rows where key(s) match value(s)
        public bool DeleteRows(string table, KeyValues keysAndVals)
        {
            //Build the SQL statement
            string sql = "DELETE FROM " + table + " WHERE (" + prepareKeysAndVals(keysAndVals, "keyVal") + ")";

            //Create the parameter binding mappings
            Dictionary<string, KeyValues> bindingMap = new Dictionary<string, KeyValues>();
            bindingMap.Add("keyVal", keysAndVals);

            //Perform the query and return whether or not it succeeded
            return PerformQuery(sql, bindingMap);
        }


        //Perform a mutator query on the database
        protected bool PerformQuery(string sql, Dictionary<string, KeyValues> bindingMap)
        {
            //If we are in verbose mode, output the SQL statement
            if (verboseMode == true)
            {
                Console.WriteLine(sql);
            }

            //Whether or not the query succeeds
            bool success = false;

            //Create a new connection
            DbConnection conn = createConnection();
            if (conn != null)
            {
                using (conn)
                {
                    //Create the command object
                    DbCommand command = createCommand(sql, conn);

                    //Loop through the parameter binding mappings
                    for (int i = 0; i < bindingMap.Count; ++i)
                    {
                        //Grab a reference to the current mappings
                        KeyValues mappings = bindingMap.Values.ElementAt(i);

                        //Loop through the parameters and values
                        for (int j = 0; j < mappings.Count; ++j)
                        {
                            //Bind the value parameter (field names CANNOT be parameterised, it breaks the query)
                            addParameter(ref command, "@" + bindingMap.Keys.ElementAt(i) + j.ToString(), mappings.Values.ElementAt(j));

                            //If we are in verbose mode, output the bindings
                            if (verboseMode == true)
                            {
                                Console.WriteLine("Mapping " + "@" + bindingMap.Keys.ElementAt(i) + j.ToString() + " to " + mappings.Values.ElementAt(j));
                            }
                        }
                    }

                        try
                        {
                            //Execute the query and retrieve the number of rows affected
                            int rowsAffected = command.ExecuteNonQuery();

                            //If at least one row was affected, the query was successful
                            if (rowsAffected > 0)
                            {
                                //Success!
                                success = true;
                            }
                        }
                        catch (Exception e)
                        {
                            //Record the error
                            errorStr = e.Message;
                        }
                }
            }
            else
            {
                Console.WriteLine("conn is NULL!");
            }

            //Report on whether or not we succeeded
            return success;
        }


        //Function to access the last error (if any) that ocurred
        public string GetLastError()
        {
            //Return the most recent error string
            return errorStr;
        }


        //Utility Functions

        //Generates the condition string for a WHERE statement from the supplied mappings
        protected string prepareKeysAndVals(KeyValues keysAndVals, string paramBase = "value", string overrideDelim = "")
        {
            //Check that the supplied dictionary is not null
            if (keysAndVals != null)
            {
                /*
                //Set the delimeter according to the KeyValues mode
                string delim = (keysAndVals.Mode == ConditionMode.ModeAnd) ? " AND " : " OR ";
                if (overrideDelim != "")
                {
                    delim = overrideDelim;
                }
                */

                /*
                //Create an array to hold the pieces of the condition string
                List<string> conditionComponents = new List<string>();
                */

                //Build the condition string
                string conditionString = "";

                //Keep track of whether or not there are any open brackets left by top-level condition modes
                bool openBrackets = false;

                //Since field names cannot be parameterised, simply parameterise the values
                for (int i = 0; i < keysAndVals.Count; ++i)
                {
                    //Use the actual key string, but create a parameter for the value
                    //conditionComponents.Add(keysAndVals.Keys.ElementAt(i) + " = @" + paramBase + i.ToString()); 

                    

                    conditionString += keysAndVals.Keys.ElementAt(i) + " = @" + paramBase + i.ToString();

                    
                    
                        //Use the condition mode, unless we are on the last item
                        if (i < (keysAndVals.Count - 1))
                        {
                            //If we are overriding the delimiter, do so
                            if (overrideDelim != "")
                            {
                                conditionString += overrideDelim;
                            }
                            else
                            {
                                switch (keysAndVals.GetMode(i))
                                {
                                    case ConditionMode.ModeOr:
                                        conditionString += " OR ";
                                        break;

                                    case ConditionMode.ModeAnd:
                                        conditionString += " AND ";
                                        break;

                                    case ConditionMode.ModeTopLevelOr:
                                        conditionString = "(" + conditionString + ") OR (";
                                        openBrackets = true;
                                        break;

                                    case ConditionMode.ModeTopLevelAnd:
                                        conditionString = "(" + conditionString + ") AND (";
                                        openBrackets = true;
                                        break;
                                }
                            }
                        }
                        else
                        {
                            if (openBrackets == true)
                            {
                                conditionString += ")";
                            }
                        }
                   
                }

                //Combine the pieces together and return the generated condition string
                //return String.Join(delim, conditionComponents.ToArray());
                return conditionString;
            }
            else
            {
                //Return an empty string
                return "";
            }
        }

        //Generates a SELECT statement, based on the given information
        protected DbCommand generateSelectStatement(string table, string[] fields, KeyValues keysAndVals, int limit, DbConnection conn)
        {
            //Prepare the WHERE condition string
            string preparedConditions = prepareKeysAndVals(keysAndVals);

            //Build the SQL statement itself
            string sql = "SELECT " + String.Join(", ", fields) + " FROM " + table + (preparedConditions.Length > 0 ? " WHERE (" + preparedConditions + ")" : "") + ";";

            //Apply the limit (if any)
            sql = applyLimit(sql, limit);

            //If we are in verbose mode, output the SQL statement
            if (verboseMode == true)
            {
                Console.WriteLine(sql);
            }

            //Create the command object
            DbCommand cmd = createCommand(sql, conn);

            //Loop through the key/value pairs in the WHERE condition string
            if (keysAndVals != null)
            {
                for (int i = 0; i < keysAndVals.Count; ++i)
                {
                    //Bind the value parameter (field names CANNOT be parameterised, it breaks the query)
                    addParameter(ref cmd, "@value" + i.ToString(), keysAndVals.Values.ElementAt(i));

                    //If we are in verbose mode, output the bindings
                    if (verboseMode == true)
                    {
                        Console.WriteLine("Mapping " + "@value" + i.ToString() + " to " + keysAndVals.Values.ElementAt(i));
                    }
                }
            }

            //Return the generated command with all parameters bound
            return cmd;
        }
    }
}
